﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_RoleDAL: IT_RoleDAL
    {
        /// <summary>
        ///  获取角色数量
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="smCode"></param>
        /// <returns></returns>
        public int GetRoleCount(string roleName, string smCode)
        {
            sb?.Clear();
            sb.Append($"SELECT COUNT(0) FROM dbo.T_Role WHERE  1=1  ");
            if (!string.IsNullOrEmpty(roleName))
                sb.Append($" AND role_name LIKE '%{Helper.strToSqlStr(roleName)}%' ");
            if (!string.IsNullOrEmpty(smCode))
                sb.Append($" AND sm_code='{smCode}' ");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        /// 获取待分配角色用户数据
        /// </summary>
        /// <param name="role_id"></param>
        /// <param name="unit_code"></param>
        /// <returns></returns>
        public List<HCQ2_Model.SelectModel.ListBoxModel> GetWaitRoleDataByPerson(int role_id, string unit_code = null)
        {
            sb?.Clear();
            sb.Append("SELECT u.user_name AS text,CAST(u.user_id AS NVARCHAR(100)) AS value FROM dbo.T_User u WHERE 1=1 ");
            if (!string.IsNullOrEmpty(unit_code))
                sb.Append($" AND unit_code='{unit_code}' ");
            sb.Append($" AND NOT EXISTS(SELECT user_id FROM dbo.T_UserRoleRelation n WHERE n.user_id=u.user_id AND n.role_id={role_id});");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<HCQ2_Model.SelectModel.ListBoxModel>(dt);
        }
        /// <summary>
        ///  获取已分配角色用户数据
        /// </summary>
        /// <param name="role_id"></param>
        /// <returns></returns>
        public List<HCQ2_Model.SelectModel.ListBoxModel> GetFineRoleDataByPerson(int role_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT text=u.user_name,CAST(u.user_id AS NVARCHAR(100)) AS value FROM dbo.T_User u
            WHERE exists(SELECT user_id FROM dbo.T_UserRoleRelation WHERE user_id=u.user_id AND role_id={role_id}); ");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<HCQ2_Model.SelectModel.ListBoxModel>(dt);
        }
        /// <summary>
        ///  排除被删除的用户角色设置
        /// </summary>
        /// <param name="role_id"></param>
        /// <param name="userid"></param>
        public void DelBatchRole(int role_id, string userid)
        {
            sb?.Clear();
            sb.Append($"DELETE FROM dbo.T_UserRoleRelation WHERE role_id={role_id} AND  user_id NOT IN({userid}); ");
            SqlHelper.ExecuteNonQuery(sb.ToString());
        }
    }
}